package com.yellowpage.tool;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.*;
import org.apache.struts2.ServletActionContext;
import sun.misc.BASE64Decoder;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.*;
import java.rmi.server.ExportException;
import java.util.*;
import java.util.List;

/**
 * Created by Sun on 2015/8/28.
 */
public class Excelexport {

    //导出excel文件，以文件格式导出excel表
    public static File exportExcelAsFile(List<Map> resultList,String sheetname,LinkedHashMap title)
    {
        Log log = LogFactory.getLog(Excelexport.class);

        //获取系统的分隔符
        String separator = File.separator;

        //给出excel存储位置
        File excelresult = new File(System.getProperty("user.dir")
                + separator +  System.currentTimeMillis() + ".xls");

        FileOutputStream fop = null;

        try
        {
                //给出了写文件的指针
                fop = new FileOutputStream(excelresult);
                String tempname[] = sheetname.split("\\.");
                sheetname = tempname[tempname.length - 1];

                //获得工作表
                HSSFWorkbook workbook = returnworkbook(resultList, sheetname, title);
                //得到函数名
                String tablename = ReturnTableName(sheetname);

                //如果发现有水印
                if (tablename != null)
                {
                    //生成水印的图片
                    File watermarkFileName = createWaterMark(tablename);
                    writewatermark(workbook,watermarkFileName);
                }

                workbook.write(fop);
                fop.close();

        }


        catch (Exception e)
        {
            log.error(e.getMessage(), e);
        }

        finally
        {
            if (fop != null) {
                try {
                    fop.close();
                } catch (IOException e) {
                    log.error(e.getMessage(), e);
                }
            }
            return excelresult;
        }
    }

    //为EXCEL导出的API函数  第一个为导出Excel的结果，第二个为表名，第三个为表头
    public static void exportExcel(List<Map> resultList,String sheetname,LinkedHashMap title)

    {
        //获得函数名,点需要转义
        String tempname[] = sheetname.split("\\.");
        sheetname = tempname[tempname.length - 1];

        Log log = LogFactory.getLog(Excelexport.class);
        OutputStream outp = null;
        try
        {


            HttpServletResponse response = ServletActionContext.getResponse();
            response.setContentType("application/x-download");
            response.addHeader("Content-Disposition", "attachment;filename="
                    + sheetname + ".xls");

            outp = response.getOutputStream();

            //获得工作表
            HSSFWorkbook workbook = returnworkbook(resultList,sheetname,title);

            //得到函数名
            String tablename = ReturnTableName(sheetname);

            //如果发现有水印
            if (tablename != null)
            {
                //生成水印的图片

                File watermarkFileName = createWaterMark(tablename);
                //File watermarkFileName = new File("E:\\test.png");
                writewatermark(workbook,watermarkFileName);

            }

            workbook.write(outp);
            response.flushBuffer();
            outp.flush();
           // response.reset();

        }
        catch (Exception e)
        {
            log.error(e.getMessage(), e);
        }

        finally
        {
            if (outp != null) {
                try {
                    outp.close();
                } catch (IOException e) {
                    log.error(e.getMessage(), e);
                }
            }
        }
    }

    //一个写入水印的函数来进行封装
    public static void writewatermark(HSSFWorkbook workbook,File watermarkFileName) throws IOException
    {
            //获取第一个sheet
            HSSFSheet sheet = workbook.getSheetAt(0);

            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();

            BufferedImage bufferImg = ImageIO.read(watermarkFileName);
            ImageIO.write(bufferImg, "png", byteArrayOut);

            //获取总行数

            //获取一个excel表格中的总记录数
            int rowNum = sheet.getLastRowNum();
            HSSFRow lastRow = sheet.getRow(rowNum);

            //取最后一行的列数
            int columnNum = lastRow.getPhysicalNumberOfCells();

            //画图的顶级管理器，一个sheet只能获取一个
            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

            //anchor主要用于设置图片的属性,起始位置
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 255, 255, (short) 2, 8, (short) 9, 16);

            patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));

            //删除临时文件
            watermarkFileName.delete();

    }


    //给出了写入workbook的函数,即写入workbook的数据
    public static HSSFWorkbook returnworkbook(List<Map> resultList,String sheetname,LinkedHashMap title)
    {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(sheetname);
        sheet.autoSizeColumn(1);
        HSSFCellStyle cellStyle = workbook.createCellStyle(); //单元格格式
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); //设置右边框
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中

        HSSFRow row1 = sheet.createRow(0);// 二级标题栏
        //String[] title = {"地区代码","地区名称","日期 ","新增数 ","活跃数","主动活跃数","被动（来显）","累计用户数 "};

        //列表迭代器,遍历姓名表
        Set<String> keyset = title.keySet();
        int order = 0;
        for(Iterator<String> iter = keyset.iterator(); iter.hasNext();)
        {
            HSSFCell cell = row1.createCell(order);
            order ++ ;
            Object key = iter.next();
            String value = String.valueOf(title.get(key));
            cell.setCellValue(value);
        }

        //放置每一个字段的值

        for (int i = 0, len = resultList.size(); i < len; i++)
        {

            //写入到log中
            HSSFRow data_row = sheet.createRow(i + 1);
            Map result = resultList.get(i);

            order = 0;
            for(Iterator iter = keyset.iterator(); iter.hasNext();)
            {
                Object key = iter.next();
                data_row.createCell(order).setCellValue(
                        result.get(key) != null ? result.get(
                                key).toString() : "");

                order ++;

            }
        }
        return  workbook;
    }


    //给出对应的表名
    public static String ReturnTableName(String functionname) throws IOException
    {



        //读取配置文件信息
        Properties pp = new Properties();
        InputStream fis = Excelexport.class.getClassLoader().getResourceAsStream("table.ini");
        pp.load(fis);
        String tablename;
        try {
            tablename  = pp.getProperty(functionname).toString();
        }

        //当获得异常的时候
        catch (Exception e)
        {
            tablename = null;
        }


        fis.close();
        //返回数据库名
        return  tablename;
    }


    //生成水印图片
    public static File createWaterMark(String watermark) throws IOException {

            //获取系统的分隔符
            String separator = File.separator;

            //暂存的水印地址
            File watermarkFileName = new File(System.getProperty("user.dir")
                 + separator + "picture" + separator + System.currentTimeMillis() + ".png");

            //当文件不存在的时候，创建目录
            if(!(watermarkFileName.exists())) {

                //  先创建文件所在的目录
                watermarkFileName.getParentFile().mkdirs();
                // 创建新文件
                watermarkFileName.createNewFile();
            }


            int width = 600; // 水印图片的宽度
            int height = 200;

            // 创建一个和原图片同大小的新空白图片,得到画笔对象g  给出一个支持透明的格式
            BufferedImage bimage = new BufferedImage(width, height, BufferedImage.TYPE_4BYTE_ABGR);

            Graphics2D g = bimage.createGraphics();

            g.setComposite(AlphaComposite.getInstance(AlphaComposite.SRC_OVER, 1f));


            g.setColor(Color.white);
            g.fillRect(0, 0, width, height);//填充整个屏幕


            // 设置字体
            Font font = new Font("SansSerif", Font.BOLD, 30);
            g.setFont(font);
            // 设置字体颜色
            g.setColor(Color.red);

            // @param watermarkAlpha 透明度表示字体透明 0最小 1最大
            float watermarkAlpha = 0.05f;
            //值从0f-1.0f
            g.setComposite(AlphaComposite.getInstance(AlphaComposite.SRC_ATOP, watermarkAlpha));
            // 画字两边 两个数字表示字体位置
            g.drawString(watermark, width / 10, height / 2);

            //设置前景透明结束
            g.setComposite(AlphaComposite.getInstance(AlphaComposite.SRC_OVER));

            //将白色背景转为透明的代码，切记切记！！！！！！！！！
            for (int j1 = bimage.getMinY(); j1 < bimage
                    .getHeight(); j1++) {
                for (int j2 = bimage.getMinX(); j2 < bimage
                        .getWidth(); j2++) {
                    int rgb = bimage.getRGB(j2, j1);

                    int R = (rgb & 0xff0000) >> 16;
                    int G = (rgb & 0xff00) >> 8;
                    int B = (rgb & 0xff);
                    if (((255 - R) < 3) && ((255 - G) < 3) && ((255 - B) < 3)) {
                        rgb = ((0 + 1) << 24) | (rgb & 0x00ffffff);
                    }

                    bimage.setRGB(j2, j1, rgb);

                }
            }

            g.dispose();
            FileOutputStream out = null;
            try {


                out = new FileOutputStream(watermarkFileName);

                ImageIO.write(bimage, "png", out);


                //设置图片质量
                //param.setQuality(100f, true);
                //encoder.encode(bimage, param);
            } catch (Exception e) {
                System.out.println("---生成失败---");

            } finally {
                if (out != null) {
                    try {
                        out.close();
                    } catch (Exception e) {
                    }
                }
            }
            //System.out.println("===生成成功===");
            return  watermarkFileName;

    }


}
